package com.quanquan.user.repository;

import com.quanquan.user.entity.SelfHelpIntegral;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 功能描述:自助积分数据层
 *
 * @ClassName: SelfHelpIntegralRepository
 * @Author: xiaoqianh丶
 * @Date: 2019-04-12 09:59
 * @Version: V1.0
 */
@Repository
public interface SelfHelpIntegralRepository extends JpaRepository<SelfHelpIntegral, String>, JpaSpecificationExecutor<SelfHelpIntegral> {

    /**
     * 根据小票的号码查询申请,
     * @param ticketNum
     * @param
     * @return
     */
  public List<SelfHelpIntegral> findByTicketNumOrderByCreateTimeDesc(String ticketNum);


    /**
     * 根据申请id和申请是否移除查询信息
     * @param id
     * @param remove
     * @return
     */
  public SelfHelpIntegral findByIdAndRemove(String id ,Integer remove);


    /**
     * 根据是否移除查询信息查找集合
     *
     * @param remove
     * @return
     */
    @Query(value = "SELECT * FROM oo_self_help_integral WHERE status_remove = ?1 AND user_id IS NULL",nativeQuery = true)
  List<SelfHelpIntegral> findByRemove(Integer remove);



    @Query(value = "SELECT * FROM oo_self_help_integral WHERE store_id = ?3 AND ticket_num = ?1 AND cosume_day = ?2", nativeQuery = true)
    public List<SelfHelpIntegral> checkSelfHelpIntegral(String ticketNum, String consumeDay, String storeId);

    @Query(value = "SELECT * FROM oo_self_help_integral where application_status ='successpass' ", nativeQuery = true)
    List<SelfHelpIntegral> findAllSelfHelpIntegral();

    /**
     * 商铺消费(笔数)排行前十
     * @param starTime
     * @param endTime
     * @return
     */
    @Query(value = "SELECT count(*) as count,s.store_name as name FROM  oo_self_help_integral d LEFT join oo_store s on d.store_id=s.id  WHERE d.create_time BETWEEN ?1 and ?2 group by d.store_id order by count desc limit 0, 10 ", nativeQuery = true)
     List<Object[]> findStoreByNum(String starTime, String endTime);



    /**
     * 商铺消费(积分)排行前十
     * @param starTime
     * @param endTime
     * @return
     */
    @Query(value = "SELECT a.integral as integral, s.store_name as name FROM (SELECT SUM(receive_point) AS integral,store_id FROM oo_self_help_integral WHERE create_time  BETWEEN ?1 and ?2 GROUP BY store_id order by integral desc) a INNER JOIN oo_store s WHERE s.id = a.store_id  limit 0, 10 ", nativeQuery = true)
    List<Object[]> findStoreByIntegral(String starTime, String endTime);


    /**
     * 商铺消费(积分)排行前十
     * @param starTime
     * @param endTime
     * @return
     */
    @Query(value = "SELECT a.money as money, s.store_name as name FROM (SELECT SUM(money) AS money,store_id FROM oo_self_help_integral WHERE create_time  BETWEEN ?1 and ?2 GROUP BY store_id order by money desc) a INNER JOIN oo_store s WHERE s.id = a.store_id  limit 0, 10 ", nativeQuery = true)
    List<Object[]> findStoreByMoney(String starTime, String endTime);



    /**
     *指定时间段内的消费金额获得数
     * @param starTime
     * @param endTime
     * @return
     */
    @Query(value = "select sum(money) from oo_self_help_integral where application_status = 'successpass' and `create_time` BETWEEN ?1 and?2", nativeQuery = true)
    Double findMoneySelfHelpByNum(Date starTime, Date endTime);



    /**
     *指定时间段内的总积分数
     * @param starTime
     * @param endTime
     * @return
     */
    @Query(value = "select sum(receive_point) from oo_self_help_integral where application_status = 'successpass' and `create_time` BETWEEN ?1 and?2", nativeQuery = true)
    Double findIntegralSelfHelpByNum(Date starTime, Date endTime);


  /**
   * 单日累计消费笔数超过8笔及以上的会员
   * @return
   */
  @Query(value = "SELECT u.id,u.name,u.phone,a.count FROM oo_user u INNER JOIN (SELECT user_id,COUNT(1) AS COUNT FROM oo_self_help_integral WHERE cosume_day = ?1 GROUP BY user_id HAVING COUNT >=8)a WHERE a.user_id = u.id",nativeQuery = true)
  List<Object[]> findOneDayOneUserMuchDo(String dateTime);

  /**
   * 单日单店累计消费超过4笔及以上的会员
   * @return
   */
  @Query(value = "SELECT u.id,u.name,u.phone,b.store_id,b.store_name,b.count FROM oo_user u INNER JOIN (SELECT a.user_id,a.store_id,a.count,s.store_name FROM (SELECT user_id,store_id,COUNT(1) AS COUNT FROM oo_self_help_integral WHERE cosume_day = ?1 GROUP BY user_id,store_id HAVING COUNT >=4) a INNER JOIN oo_store s WHERE s.id = a.store_id) b WHERE b.user_id = u.id",nativeQuery = true)
  List<Object[]> findOneDayOneUserOneDo(String dateTime);

  /**
   * 自然周单店累计消费超过10笔及以上的会员
   * @return
   */
  @Query(value = "SELECT u.id,u.name,u.phone,b.store_id,b.store_name,b.count FROM oo_user u INNER JOIN (SELECT a.user_id,a.store_id,a.count,s.store_name FROM (SELECT user_id,store_id,COUNT(1) AS COUNT FROM oo_self_help_integral WHERE cosume_day BETWEEN ?1 AND ?2 GROUP BY user_id,store_id HAVING COUNT >=10) a INNER JOIN oo_store s WHERE s.id = a.store_id) b WHERE b.user_id = u.id",nativeQuery = true)
  List<Object[]> findOneWeekOneUserOneDo(String startTime,String endTime);

}


